from fastapi import APIRouter, status, Depends,HTTPException
from sqlalchemy.orm import selectinload
from sqlmodel import SQLModel, Session, select, func, or_
from config.Database import engine,get_session
import models.models as models
import models.crud as curd
import models.schemas as schemas

# 创建所有定义的表
SQLModel.metadata.create_all(engine)

router = APIRouter()

@router.get("/")
async def root():
    return {"message": "SQLModel"}

@router.post("/users/v2/", response_model=schemas.UserResponse)
async def get_users_v2(user:schemas.UserCreate, session:Session = Depends(get_session)):
    userIn = models.User.model_validate(user)
    db_user = curd.create_user(session, userIn)
    return schemas.UserResponse.model_validate(db_user.model_dump())

@router.post("/users",response_model=models.User, status_code=status.HTTP_201_CREATED)
async def create_user(user: models.User, session:Session = Depends(get_session)):
    """
    创建用户
    - user: 用户数据
    """
    # 检查邮箱是否在数据库中已存在
    statement = select(models.User).where(models.User.email == user.email)
    existing_user = session.exec(statement).first()
    if existing_user:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"邮箱 {user.email} 已存在"
        )
    print(user)
    # 添加用户到数据库
    session.add(user)
    session.commit()    # 提交事务
    session.refresh(user)   # 刷新获取自动生成的字段（如：id)
    return user

@router.get("/users",response_model=list[models.User])
async def get_users(page_index: int = 1, page_size: int = 10, session:Session = Depends(get_session)):
    """
    获取用户列表
    - page_index: 页码
    - page_size: 每页数量
    """
    start_index = (page_index - 1) * page_size
    statement = select(models.User).offset(start_index).limit(page_size)
    users = session.exec(statement).all()
    return users

@router.get("/users/{user_id}", response_model=models.User)
async def get_user(user_id: int, session:Session = Depends(get_session)):
    """
    获取用户
    - user_id: 用户ID
    """
    # statement = select(models.User).where(models.User.id == user_id)
    # user = session.exec(statement).first()
    user = session.get(models.User, user_id)
    if not user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"用户 {user_id} 不存在"
        )
    return user

@router.put("/users/{user_id}", response_model=models.User)
async def update_user(user_id: int, user: models.User, session:Session = Depends(get_session)):
    """
    更新用户
    - user_id: 用户ID
    - user: 用户数据
    """
    # 获取要更新的用户
    # db_user = session.get(models.User, user_id)
    # 下面使用.with_for_update()添加行锁保证线程安全
    statement = select(models.User).where(models.User.id == user_id).with_for_update()
    db_user = session.exec(statement).first()
    if not db_user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"用户 {user_id} 不存在"
        )
    # 将更新数据转换为字典，排除未设置的字段
    update_data = user.model_dump(exclude_unset=True)
    # 更新用户属性
    for key, value in update_data.items():
        if key != "id":
            setattr(db_user, key, value)

    session.add(db_user)
    session.commit()
    session.refresh(db_user)
    return db_user

@router.delete("/users/{user_id}")
async def delete_user(user_id: int, session:Session = Depends(get_session)):
    """
    删除用户
    - user_id: 用户ID
    """
    # 获取要删除的用户
    # db_user = session.get(models.User, user_id)
    statement = select(models.User).where(models.User.id == user_id).with_for_update()
    db_user = session.exec(statement).first()
    if not db_user:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"用户 {user_id} 不存在"
        )
    session.delete(db_user)
    session.commit()
    return {
        "message": f"用户 {user_id} 删除成功"
    }

# ==================== 多表操作 ==========================
@router.post("/departments",response_model=models.Department, status_code=status.HTTP_201_CREATED)
async def create_department(
        department: models.Department,
        session:Session = Depends(get_session)
):
    """
    创建部门
    - department: 部门数据
    """
    session.add(department)
    session.commit()
    session.refresh(department)
    return department

@router.get("/departments",response_model=list[models.Department])
async def get_departments(
        page_index: int = 1,
        page_size: int = 10,
        session:Session = Depends(get_session)):
    """
    获取部门列表
    - page_index: 页码
    - page_size: 每页记录条数
    """
    # 获取部门表的总记录数
    total = session.exec(select(func.count(models.Department.id))).first()
    # total除页大小向上取整，获取总页数
    total_page = (total + page_size - 1) // page_size
    if page_index > total_page:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"页码 {page_index} 超过总页数 {total_page}"
        )

    statement = select(models.Department).offset((page_index - 1) * page_size).limit(page_size)
    departments = session.exec(statement).all()
    return departments

@router.get("/departments/{department_id}")
async def read_department(
        department_id: int,
        session:Session = Depends(get_session)):
            """
            获取部门
            - department_id: 部门ID
            """
            statement = select(models.Department).where(models.Department.id == department_id)
            department = session.exec(statement).first()
            if not department:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail=f"部门 {department_id} 不存在"
                )
            return department.model_dump() | {"employees": department.employees}


@router.post("/employees", response_model=models.Employee, status_code=status.HTTP_201_CREATED)
async def create_employee(
        employee: models.Employee,
        session:Session = Depends(get_session)
):
    """
    创建员工
    - employee: 员工数据
    """
    # 检查部门是否存在
    if employee.department_id:
        department = session.get(models.Department, employee.department_id)
        if not department:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"部门 {employee.department_id} 不存在"
            )
    # 检查员工号是否存在
    existing_employee = session.exec(
        select(models.Employee).where(models.Employee.employee_id == employee.employee_id)
    ).first()
    if existing_employee:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"员工号 {employee.employee_id} 已存在"
        )
    session.add(employee)
    session.commit()
    session.refresh(employee)
    return employee


@router.get("/employees",response_model=list[models.Employee])
async def get_employees(
        page_index: int = 1,
        page_size: int = 10,
        session:Session = Depends(get_session)):
    """
    获取员工列表
    - page_index: 页码
    - page_size: 每页记录条数
    """
    # 获取员工表总记录数
    total = session.exec(select(func.count(models.Employee.id))).first()
    # total除页大小向上取整，获取总页数
    total_page = (total + page_size - 1) // page_size
    if page_index > total_page:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"页码 {page_index} 超过总页数 {total_page}"
        )
    statement = select(models.Employee).offset((page_index - 1) * page_size).limit(page_size)
    employees = session.exec(statement).all()
    return employees

# ============= 复杂关联查询 ================
@router.get("/reports/employees-by_department")
async def get_employees_by_department(
        session:Session = Depends(get_session)
):
    """
    按部门统计员工数量
    """
    # 这里要使用到join和group_by进行聚合查询
    # 这里join使用的是 inner join 关联是使用的外键关联（如果我们没有在模型中指定外键关系则需要在这里手动指定）
    statement = (select(
        models.Department.name,
        func.count(models.Employee.id).label('employee_count')
    )
        # 如果们的模型在定义时指定了外键的则这里会使用默认的外键关联
        # .join(models.Employee).group_by(models.Department.name))
        # join关联并明确指定关联的字段
        # isouter=True 表示使用左外连接 （注意：没有右外连接的，如果需要则调换顺序来实现）
        .join(models.Employee, models.Department.id == models.Employee.department_id,isouter=True)
        .group_by(models.Department.name))  # 指定group by的字段
    results = session.exec(statement).all()

    return [
        {
            "department": row[0],
            "employee_count": row[1]
        } for row in results
    ]

@router.get("/reports/departments/{department_id}/employees")
async def get_department_employees(
        department_id: int,
        session:Session = Depends(get_session)
):
    """
    获取部门下的员工列表
    """
    # 检查部门是否存在
    department = session.get(models.Department, department_id)
    if not department:
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"部门 {department_id} 不存在"
        )
    statement = select(models.Employee).where(models.Employee.department_id == department_id)
    employees = session.exec(statement).all()
    return employees


@router.get("/employees/search")
async def search_employees(
        first_name: str|None = None,
        last_name: str|None = None,
        department_id: int|None = None,
        min_salary: float | None = None,
        max_salary: float | None = None,
        session:Session = Depends(get_session)
):
    """
    员工搜索
    - first_name: first_name 查询条件，可以为空
    - last_name: last_name 查询条件，可以为空
    - department_id: 部门id查询条件，可以为空
    - min_salary: 最小salary,查询条件，可以为空
    - max_salary: 最大salary,查询条件，可以为空
    """
    # 构建查询语句
    statement = select(models.Employee)
    # 根据传入的参数动态构建
    # 这里的like 模糊匹配转为sql则是like %xxx%
    # 这里的contains则转为 like concat('%%',xxx, '%%')
    # if first_name:
    #     statement = statement.where(models.Employee.first_name.like(f"%{first_name}%"))
    # if last_name:
    #     statement = statement.where(models.Employee.last_name.contains(last_name))
    # 默认使用where是and关系，如果我们要使用or关系可以如下
    if first_name and last_name:
        # 现在这里就是把其中的两个条件转为了or关系
        # 转为sql类似于：employee.first_name LIKE %(first_name_1)s OR (employee.last_name LIKE concat('%%', %(last_name_1)s, '%%'))
        statement = statement.where(or_(
            models.Employee.first_name.like(f"%{first_name}%"),
            models.Employee.last_name.contains(last_name)
        ))
    if department_id:
        statement = statement.where(models.Employee.department_id == department_id)
    if min_salary:
        statement = statement.where(models.Employee.salary >= min_salary)
    if max_salary:
        statement = statement.where(models.Employee.salary <= max_salary)

    employees = session.exec(statement).all()
    return employees

